import mssql

vbkeys =["AddHandler","AddressOf","AndAlso","Alias","And","Ansi","As","Assembly","Auto","Boolean",
                "ByRef","Byte","ByVal","Call","Case","Catch","CBool","CByte","CChar",
                "CDate","CDec","CDbl","Char","CInt","Class","CLng","CObj","Const",
                "CShort","CSng","CStr","CType","Date","Decimal","Declare","Default",
                "Delegate","Dim","DirectCast","Do","Double","Each","Else","ElseIf","End",
                "Enum","Erase","Error","Event","Exit","False",
                "Finally","For","Friend","Function","Get","GetType","GoTo","Handles","If",
                "Implements","Imports","In","Inherits","Integer","Interface",
                "Is","Let","Lib","Like","Long","Loop","Me","Mod","Module",
                "MustInherit","MustOverride","MyBase","MyClass","Namespace","New","Next","Not","Nothing",
                "NotInheritable","NotOverridable","Object","On","Option","Optional","Or","OrElse",
                "Overloads","Overridable","Overrides","ParamArray","Preserve","Private","Property","Protected","Public",
                "RaiseEvent","ReadOnly","ReDim","RemoveHandler","Resume","Return",
                "Select","Set","Shadows","Shared","Short","Single","Static","Step","Stop",
                "String","Structure","Sub","SyncLock","Then","Throw",
                "To","True","Try","TypeOf","Unicode","Until","Variant","When","While",
                "With","WithEvents","WriteOnly","Xor"]

tables = mssql.exeTable("select * from dbo.sysobjects where xtype='U'")
c = file("models.vb", "w+")

def FormatClassName (name):
    name = name.capitalize()
    if name.endswith("ies"):
        return name[:-3] + "y"
    if name.endswith("s"):
        return name[:-1]
    return name
    

for dr in tables:
    sql = "select c.name from sys.columns c where column_id=1 and object_id=" + str(dr.id)
    pk = mssql.exeScalar(sql)
    if pk.lower()!="id":
        c.write('<Entity(PrimaryKeyName:="%s",TableName:="%s")> _' % (pk, dr.name))
    else:
        c.write('<Entity(TableName:="%s")> _' % (dr.name))
    c.write('\n')
    c.write("Public Class %s" % (FormatClassName(dr.name)))
    c.write('\n')
    c.write("    Inherits Entity")
    c.write('\n')
    c.write("")
    c.write('\n')

    sql = "select c.name, c.max_length, T.name as typename from sys.columns c inner join sys.types T on c.user_type_id= T.user_type_id where column_id>1 and object_id="
    cols = mssql.exeTable(sql + str(dr.id))
    for col in cols:
        if col.typename =="int":
            c.write("    Public %s As Field.int" %(col.name))
            c.write('\n')
        elif col.typename=="nvarchar":
            c.write("    <Field(Size:=%d, ColumnType:=SqlDbType.NVarChar)> _" %(col.max_length))
            c.write('\n')
            c.write("    Public %s As Field.text" %(col.name))
            c.write('\n')
        elif col.typename=="varchar":
            c.write("    <Field(Size:=%d, ColumnType:=SqlDbType.VarChar)> _" %(col.max_length))
            c.write('\n')
            c.write("    Public %s As Field.text" %(col.name))
            c.write('\n')
        elif col.typename=="ntext":
            c.write("    <Field(ColumnType:=SqlDbType.NText)> _")
            c.write('\n')
            c.write("    Public %s As Field.text" %(col.name))
            c.write('\n')
        elif col.typename=="text":
            c.write("    <Field(ColumnType:=SqlDbType.Text)> _")
            c.write('\n')
            c.write("    Public %s As Field.text" %(col.name))
            c.write('\n')
        elif col.typename=="nchar":
            c.write("    <Field(Size:=%d, ColumnType:=SqlDbType.NChar)> _" %(col.max_length))
            c.write('\n')
            c.write("    Public %s As Field.text" %(col.name))
            c.write('\n')
        elif col.typename=="char":
            c.write("    <Field(Size:=%d, ColumnType:=SqlDbType.Char)> _" %(col.max_length))
            c.write('\n')
            c.write("    Public %s As Field.text" %(col.name))
            c.write('\n')
        elif col.typename=="decimal":
            c.write("    Public %s As Field.decimal" %(col.name))
            c.write('\n')
        else:
            c.write("    Public %s As Field.%s" %(col.name, col.typename))
            c.write('\n')

    c.write("")
    c.write('\n')
    c.write("End Class")
    c.write('\n')
    c.write('\n')

c.close()
    